package com.snail.generator.mapper.impl;

import com.snail.generator.entity.ColumnInfo;
import com.snail.generator.entity.TableInfo;
import com.snail.generator.mapper.GeneratorMapper;
import com.snail.generator.utils.SqlUtils;
import lombok.SneakyThrows;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@Repository
public class PostgresqlGeneratorMapper implements GeneratorMapper {

    @Override
    @SneakyThrows
    public List<String> queryDatabase() {
        String sql = "select datname from pg_database where datname not in ('template0', 'template1')";
        List<String> databaseList = new ArrayList<>();
        SqlUtils.execute(sql, resultSet -> {
            while (resultSet.next()) databaseList.add(resultSet.getString(1));
        });
        return databaseList;
    }

    @Override
    @SneakyThrows
    public List<TableInfo> queryTable() {
        String sql = """
                select concat(schemaname, '.', tablename), obj_description(relfilenode, 'pg_class')
                from pg_tables t1 join pg_class t2 on t1.tablename = t2.relname
                where schemaname not in ('pg_catalog', 'information_schema')
                """;
        List<TableInfo> tableList = new ArrayList<>();
        SqlUtils.execute(sql, resultSet -> {
            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo(resultSet.getString(1), resultSet.getString(2));
                tableList.add(tableInfo);
            }
        });
        return tableList;
    }

    @Override
    public List<ColumnInfo> queryColumn(String tableName) {
        String sql = """
                select t1.relname as table_name, t2.attname as column_name, pg_type.typname as data_type,
                    col_description(t2.attrelid, t2.attnum) as column_comment,
                    (case t3.contype when 'p' then 'PRI' else '' end) as column_key,
                    (case
                        when pg_get_expr(t4.adbin, t4.adrelid) like 'nextval(%::regclass)' then 'auto_increment'
                        else ''
                    end) as extra
                from pg_class as t1, pg_attribute as t2
                   inner join pg_type on pg_type.oid = t2.atttypid
                   left join pg_constraint t3 on t2.attnum = t3.conkey[1] and t2.attrelid = t3.conrelid
                   left join pg_attrdef t4 ON t2.attrelid = t4.adrelid AND t2.attnum = t4.adnum
                where t2.attrelid = ?::regclass and t2.attrelid = t1.oid and t2.attnum > 0
                """;
        List<ColumnInfo> columnList = new ArrayList<>();
        SqlUtils.execute(sql, Collections.singletonList(tableName), resultSet -> {
            while (resultSet.next()) {
                ColumnInfo columnInfo = new ColumnInfo(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getString(6)
                );
                columnList.add(columnInfo);
            }
        });
        return columnList;
    }
}
